﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Fintech.Utils;

namespace PlainRowByRowParser
{
    class Program
    {
        private static List<string> _valuesList = new List<string>();
        private static void PopulateValuesList(string fileName)
        {
            var stream = File.OpenText(fileName);
            string content = stream.ReadToEnd();
            var values = content.Split(Environment.NewLine.First());
            values.ForEach(v => _valuesList.Add(v.Trim('\n')));
        }

        static void Main(string[] args)
        {
            SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();
            connectionStringBuilder.DataSource = "(local)\\SQLEXPRESS";
            connectionStringBuilder.InitialCatalog = "ZOVNP";
            connectionStringBuilder.IntegratedSecurity = true;
            SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

            PopulateValuesList("materials.txt");

            SqlTransaction transaction = null;
            try
            {
                connection.Open();
                transaction = connection.BeginTransaction();
                foreach (var value in _valuesList)
                {
                    SqlCommand command = new SqlCommand("INSERT INTO [ZOVNP].[dbo].[Material] ([ID],[Name],[Description]) VALUES (NEWID(),@name,@description)", connection);
                    command.Transaction = transaction;
                    command.Parameters.AddWithValue("@name", value);
                    command.Parameters.AddWithValue("@description", "");
                    command.ExecuteNonQuery();
                }
                
                transaction.Commit();
            }
            catch(Exception)
            {
                transaction.Rollback();
            }
            finally
            {
                transaction.Dispose();
                connection.Close();
            }
        }
    }
}
